﻿#region Imported Namespaces
using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.IO;
using System.Security.Cryptography;

using PHSRAG.Insight21.InsightUtilities;
using BO = PHSRAG.Insight21.BusinessObjects;
using DataAccess = PHSRAG.Utility.DataAccess;
using ICachePolicy = PHSRAG.Insight21.Policy.ICachePolicy;
using SearchCriteria = PHSRAG.Insight21.SearchContext.SearchCriteria;
using ModuleKey = PHSRAG.Insight21.BusinessObjects.ModuleKey;
#endregion

namespace PHSRAG.Insight21.Agreements
{
    public class AdvanceFundRequest
    {
        /// <summary>
        /// This method fetches the Advance Requests that will be bound to the grid.				
        /// </summary>
        /// <param name="connectionString">Database connection string</param>
        /// <returns>Datatable(details)</returns>
        public DataTable GetAdvanceFundRequest(string connectionString, int StartPageIndex, int EndingPageIndex, int PageSize, string SortOrder, int loggedInUserId)
        {
            DataTable dt = null;
            int Action = 1;
            string Imageurl = "~/Images/b_view.gif";
            try
            {
                DataColumn dc1 = new DataColumn("Action");
                DataColumn dc2 = new DataColumn("ImageUrl");

                dt = DataAccess.GetDataTable(
                        connectionString, "dbo.GetAdvanceFundRequest", DataAccess.BuildSqlParameterArray(
                        "@fwkDomainUserId", SqlDbType.Int, loggedInUserId,
                        "@OrderBy", SqlDbType.VarChar, SortOrder,
                        "@PageSize", SqlDbType.Int, PageSize,
                        "@PageStartIndex", SqlDbType.Int, StartPageIndex,
                        "@PageEndIndex", SqlDbType.Int, EndingPageIndex));

                dt.Columns.Add(dc1);
                dt.Columns.Add(dc2);
                foreach (DataRow dr in dt.Rows)
                {
                    Action = 1;
                    Imageurl = "~/Images/b_view.gif";
                    if (Convert.ToInt32(dr["RequestStatusType"].ToString()) == 1)
                    {
                        if (Convert.ToInt32(dr["CreatedBy"].ToString()) == loggedInUserId)
                        {
                            Action = 2; // 1 = VIEW; 2 = EDIT; 3 = APPROVE
                            Imageurl = "~/Images/b_edit.gif";
                        }
                    }
                    else if (Convert.ToInt32(dr["RequestStatusType"].ToString()) == 2)
                    {
                        if (Convert.ToInt32(dr["PIId"].ToString() == "" ? "0" : dt.Rows[0]["PIId"].ToString()) == loggedInUserId)
                        {
                            Action = 3;
                            Imageurl = "~/Images/b_approve.gif";
                        }
                    }
                    else if (Convert.ToInt32(dr["RequestStatusType"].ToString()) == 3)
                    {
                        if (Convert.ToInt32(dr["ChiefId"].ToString() == "" ? "0" : dt.Rows[0]["ChiefId"].ToString()) == loggedInUserId || Convert.ToInt32(dr["DelegateId"].ToString() == "" ? "0" : dt.Rows[0]["DelegateId"].ToString()) == loggedInUserId)
                        {
                            Action = 3;
                            Imageurl = "~/Images/b_approve.gif";
                        }
                    }
                    else if (Convert.ToInt32(dr["RequestStatusType"].ToString()) == 4)
                    {
                        if (Convert.ToInt32(dr["GAId"].ToString() == "" ? "0" : dt.Rows[0]["GAId"].ToString()) == loggedInUserId)
                        {
                            Action = 3;
                            Imageurl = "~/Images/b_approve.gif";
                        }
                    }
                    dr["Action"] = Action.ToString();
                    dr["ImageUrl"] = Imageurl;
                }
                dt.AcceptChanges();                
            }
            catch (Exception ex)
            {
                Utility.ExceptionReport.WriteToEventLog("Failed to get the Advance Requests, Error occurred while executing the AdvanceFundRequest.GetAdvanceFundRequest", ex, true, Utility.ExceptionReport.ReportType.Error);
                throw;
            }
            return dt;
        }

        public void DeleteFundRequest(string connectionString, string PropNumber, int loggedInUserId)
        {
            try
            {
                DataAccess.ExecuteNonQuery(
                        connectionString, "dbo.DeleteAdvanceFundRequest", DataAccess.BuildSqlParameterArray(
                        "@fwkDomainUserId", SqlDbType.Int, loggedInUserId,
                        "@PropNumber", SqlDbType.VarChar, PropNumber));
            }
            catch (Exception ex)
            {
                Utility.ExceptionReport.WriteToEventLog("Failed to delete the Advance Request, Error occurred while executing the AdvanceFundRequest.DeleteFundRequest", ex, true, Utility.ExceptionReport.ReportType.Error);
                throw;
            }
        }

        public void DeleteFundRequestDocument(string connectionString, int DocId)
        {
            try
            {
                DataAccess.ExecuteNonQuery(
                        connectionString, "dbo.DeleteAdvanceFundRequestDocument", DataAccess.BuildSqlParameterArray(
                        "@DocId", SqlDbType.Int, DocId));
            }
            catch (Exception ex)
            {
                Utility.ExceptionReport.WriteToEventLog("Failed to delete the Advance Request Document, Error occurred while executing the AdvanceFundRequest.DeleteFundRequestDocument", ex, true, Utility.ExceptionReport.ReportType.Error);
                throw;
            }
        }

        public void SaveFundRequest(string connectionString, string PropNumber, int loggedInUserId, string Comment, int StatusType)
        {
            try
            {
                DataAccess.ExecuteNonQuery(
                        connectionString, "dbo.SaveAdvanceFundRequest", DataAccess.BuildSqlParameterArray(
                        "@fwkDomainUserId", SqlDbType.Int, loggedInUserId,
                        "@PropNumber", SqlDbType.VarChar, PropNumber,
                        "@Comment", SqlDbType.VarChar, Comment,
                        "@StatusType", SqlDbType.Int, StatusType));
            }
            catch (Exception ex)
            {
                Utility.ExceptionReport.WriteToEventLog("Failed to save the Advance Request, Error occurred while executing the AdvanceFundRequest.SaveFundRequest", ex, true, Utility.ExceptionReport.ReportType.Error);
                throw;
            }
        }

        public void SaveFundRequestEmailNotification(string connectionString, string PropNumber, int EmailedToId, int EmailedById, string Subject, string MessageBody, int StatusType)
        {
            try
            {
                DataAccess.ExecuteNonQuery(
                        connectionString, "dbo.SaveAdvanceFundRequestEmailNotification", DataAccess.BuildSqlParameterArray(
                        "@fwkDomainUserId", SqlDbType.Int, EmailedById,
                        "@EmailedToId", SqlDbType.Int, EmailedToId,
                        "@PropNumber", SqlDbType.VarChar, PropNumber,
                        "@Subject", SqlDbType.VarChar, Subject,
                        "@MessageBody", SqlDbType.VarChar, MessageBody,
                        "@StatusType", SqlDbType.Int, StatusType));
            }
            catch (Exception ex)
            {
                Utility.ExceptionReport.WriteToEventLog("Failed to save the Advance Request, Error occurred while executing the AdvanceFundRequest.SaveFundRequest", ex, true, Utility.ExceptionReport.ReportType.Error);
                throw;
            }
        }

        public void ApproveFundRequest(string connectionString, string PropNumber, int loggedInUserId, string FundNumber, int StatusType)
        {
            try
            {
                DataAccess.ExecuteNonQuery(
                        connectionString, "dbo.ApproveAdvanceFundRequest", DataAccess.BuildSqlParameterArray(
                        "@fwkDomainUserId", SqlDbType.Int, loggedInUserId,
                        "@PropNumber", SqlDbType.VarChar, PropNumber,
                        "@FundNumber", SqlDbType.VarChar, FundNumber,
                        "@StatusType", SqlDbType.Int, StatusType));
            }
            catch (Exception ex)
            {
                Utility.ExceptionReport.WriteToEventLog("Failed to approve the Advance Request, Error occurred while executing the AdvanceFundRequest.ApproveFundRequest", ex, true, Utility.ExceptionReport.ReportType.Error);
                throw;
            }
        }

        public void DeclineFundRequest(string connectionString, string PropNumber, int loggedInUserId, string Reason, int StatusType)
        {
            try
            {
                DataAccess.ExecuteNonQuery(
                        connectionString, "dbo.DeclineAdvanceFundRequest", DataAccess.BuildSqlParameterArray(
                        "@fwkDomainUserId", SqlDbType.Int, loggedInUserId,
                        "@PropNumber", SqlDbType.VarChar, PropNumber,
                        "@Reason", SqlDbType.VarChar, Reason,
                        "@StatusType", SqlDbType.Int, StatusType));
            }
            catch (Exception ex)
            {
                Utility.ExceptionReport.WriteToEventLog("Failed to decline the Advance Request, Error occurred while executing the AdvanceFundRequest.DeclineFundRequest", ex, true, Utility.ExceptionReport.ReportType.Error);
                throw;
            }
        }

        public int SaveDocumentMetaData(string connectionString, string PropNumber, int loggedInUserId, string FileName)
        {
            try
            {
                DataTable dt = DataAccess.GetDataTable(
                        connectionString, "dbo.SaveAdvanceFundRequestUploadedDocumentMetadata", DataAccess.BuildSqlParameterArray(
                        "@fwkDomainUserId", SqlDbType.Int, loggedInUserId,
                        "@PropNumber", SqlDbType.VarChar, PropNumber,
                        "@FileName", SqlDbType.VarChar, FileName));

                if (dt.Rows.Count > 0)
                {
                    return Convert.ToInt32(dt.Rows[0]["Id"].ToString());
                }
                else
                {
                    return 0;
                }
            }
            catch (Exception ex)
            {
                Utility.ExceptionReport.WriteToEventLog("Failed to store the document Meatadata in Advance Request, Error occurred while executing the AdvanceFundRequest.SaveDocumentMetaData", ex, true, Utility.ExceptionReport.ReportType.Error);
                throw;
            }
        }

        public void UpdateDocumentMetaData(string connectionString, int DocId, string FileUrl, int OpState)
        {
            try
            {
                DataAccess.ExecuteNonQuery(
                        connectionString, "dbo.UpdateAdvanceFundRequestUploadedDocumentMetadata", DataAccess.BuildSqlParameterArray(
                        "@DocId", SqlDbType.Int, DocId,
                        "@FileUrl", SqlDbType.VarChar, FileUrl,
                        "@OpState", SqlDbType.Int, OpState));
            }
            catch (Exception ex)
            {
                Utility.ExceptionReport.WriteToEventLog("Failed to update the Advance Request, Error occurred while executing the AdvanceFundRequest.UpdateDocumentMetaData", ex, true, Utility.ExceptionReport.ReportType.Error);
                throw;
            }
        }

        public DataTable GetInfoByProposalNumber(string connectionString, string PropNumber)
        {
            try
            {
                return DataAccess.GetDataTable(
                        connectionString, "dbo.GetAdvanceFundRequestInfoByProposalNumber", DataAccess.BuildSqlParameterArray(
                        "@PropNumber", SqlDbType.VarChar, PropNumber));
            }
            catch (Exception ex)
            {
                Utility.ExceptionReport.WriteToEventLog("Failed to get the Advance Request, Error occurred while executing the AdvanceFundRequest.GetInfoByProposalNumber", ex, true, Utility.ExceptionReport.ReportType.Error);
                throw;
            }
        }

        public DataTable GetCommentsByProposalNumber(string connectionString, string PropNumber)
        {
            try
            {
                return DataAccess.GetDataTable(
                        connectionString, "dbo.GetAdvanceFundRequestCommentsByProposalNumber", DataAccess.BuildSqlParameterArray(
                        "@PropNumber", SqlDbType.VarChar, PropNumber));
            }
            catch (Exception ex)
            {
                Utility.ExceptionReport.WriteToEventLog("Failed to get the Advance Request, Error occurred while executing the AdvanceFundRequest.GetCommentsByProposalNumber", ex, true, Utility.ExceptionReport.ReportType.Error);
                throw;
            }
        }

        public DataTable GetUploadedDocument(string connectionString, string PropNumber)
        {
            try
            {
                return DataAccess.GetDataTable(
                        connectionString, "dbo.GetAdvanceFundRequestUploadedDocumentByProposalNumber", DataAccess.BuildSqlParameterArray(
                        "@PropNumber", SqlDbType.VarChar, PropNumber));
            }
            catch (Exception ex)
            {
                Utility.ExceptionReport.WriteToEventLog("Failed to get the Advance Request, Error occurred while executing the AdvanceFundRequest.GetCommentsByProposalNumber", ex, true, Utility.ExceptionReport.ReportType.Error);
                throw;
            }
        }

        public DataTable GetApprovalsByProposalNumber(string connectionString, string PropNumber)
        {
            try
            {
                return DataAccess.GetDataTable(
                        connectionString, "dbo.GetAdvanceFundRequestApprovalsByProposalNumber", DataAccess.BuildSqlParameterArray(
                        "@PropNumber", SqlDbType.VarChar, PropNumber));
            }
            catch (Exception ex)
            {
                Utility.ExceptionReport.WriteToEventLog("Failed to get the Advance Request, Error occurred while executing the AdvanceFundRequest.GetApprovalsByProposalNumber", ex, true, Utility.ExceptionReport.ReportType.Error);
                throw;
            }
        }

        public DataTable GetProposalNumberList(string connectionString, int loggedInUserId, string prefix, int cnt)
        {
            try
            {
                return DataAccess.GetDataTable(
                        connectionString, "dbo.GetAdvanceFundRequestProposalNumberList", DataAccess.BuildSqlParameterArray(
                        "@fwkDomainUserId", SqlDbType.Int, loggedInUserId,
                        "@prefix", SqlDbType.VarChar, prefix));
            }
            catch (Exception ex)
            {
                Utility.ExceptionReport.WriteToEventLog("Failed to get the Proposal Numbers, Error occurred while executing the AdvanceFundRequest.GetProposalNumberList", ex, true, Utility.ExceptionReport.ReportType.Error);
                throw;
            }
        }
    }
}
